using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using LCL;
namespace LCL.LData
{
    /*
      var parameters = new[]{
                new MySqlParameter(){ ParameterName="foo", Value="hello" },
                new MySqlParameter(){ ParameterName="bar", Value="World" }
            };
     */
    /// <summary>
    /// 通用数据库访问类，封装了对数据库的常见操作
    ///</summary>
    [DebuggerDisplay("{ConnectionString}")]
    public sealed class DbAccesser
    {
        private string ConnectionString { get; set; }
        private LDbType ProviderType { get; set; }
        public DbConnection Connection { get; set; }
        private DbProviderFactory providerFactory;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="providerType">数据库类型枚举，参见<paramref name="providerType"/></param>
        public DbAccesser(string connectionString, LDbType providerType)
        {
            ConnectionString = connectionString;
            ProviderType = providerType;
            providerFactory = ProviderFactory.GetDbProviderFactory(providerType, connectionString);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
            }
        }
        /// <summary>   
        /// 对数据库执行增删改操作，返回受影响的行数。   
        /// </summary>   
        /// <param name="sql">要执行的增删改的SQL语句</param>   
        /// <param name="parameters">执行增删改语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, IList<DbParameter> parameters = null,
            CommandType commandType = CommandType.Text)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                command.Connection.Open();
                int affectedRows = command.ExecuteNonQuery();
                command.Connection.Close();
                return affectedRows;
            }
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个关联的DataReader实例   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns> 
        public DbDataReader ExecuteReader(string sql, IList<DbParameter> parameters = null,
            CommandType commandType = CommandType.Text)
        {
            DbCommand command = CreateDbCommand(sql, parameters, commandType);
            command.Connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>   
        /// 执行一个查询语句，返回一个包含查询结果的DataTable   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, IList<DbParameter> parameters = null,
            CommandType commandType = CommandType.Text)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable data = new DataTable();
                    data.TableName = Guid.NewGuid().ToString();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        /// <summary>   
        /// 执行一个查询语句，返回查询结果的第一行第一列   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>   
        public Object ExecuteScalar(string sql, IList<DbParameter> parameters = null,
            CommandType commandType = CommandType.Text)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                command.Connection.Open();
                object result = command.ExecuteScalar();
                command.Connection.Close();
                return result;
            }
        }
        public DbParameter CreateDbParameter(string name, object value)
        {
            return CreateDbParameter(name, ParameterDirection.Input, value);
        }
        public DbParameter CreateDbParameter(string name, ParameterDirection parameterDirection, object value)
        {
            DbParameter parameter = providerFactory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = parameterDirection;
            return parameter;
        }
        /// <summary>
        /// 创建一个DbCommand对象
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>
        /// <param name="commandType">执行的SQL语句的类型</param>
        /// <returns></returns>
        private DbCommand CreateDbCommand(string sql, IList<DbParameter> parameters = null,
            CommandType commandType = CommandType.Text)
        {
            this.Connection = providerFactory.CreateConnection();
            DbCommand command = providerFactory.CreateCommand();
            this.Connection.ConnectionString = ConnectionString;
            command.CommandText = sql;
            command.CommandType = commandType;
            command.Connection = this.Connection;
            if (!(parameters == null || parameters.Count == 0))
            {
                foreach (DbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            #region LogNet
            var content = sql;
            if (parameters != null && parameters.Count > 0)
            {
                var pValues = parameters.Select(p =>
                {
                    var value = p.Value;
                    if (value is string)
                    {
                        value = '"' + value.ToString() + '"';
                    }
                    return value;
                });
                content += Environment.NewLine + "Parameters:" + string.Join(",", pValues);
            }
            //content = "\r\nConnectionString:  " + ConnectionString +"\r\n" + content + "\r\n";
            Logger.Debug(ConnectionString,content);
            #endregion
            return command;
        }
    }

    /// <summary>
    /// DbProviderFactory工厂类
    /// </summary>
    internal class ProviderFactory
    {
        private static Dictionary<LDbType, string> providerInvariantNames = new Dictionary<LDbType, string>();
        private static Dictionary<LDbType, DbProviderFactory> providerFactoies = new Dictionary<LDbType, DbProviderFactory>(20);
        static ProviderFactory()
        {
            //加载已知的数据库访问类的程序集
            providerInvariantNames.Add(LDbType.SQLServer, "System.Data.SqlClient");
            providerInvariantNames.Add(LDbType.OleDb, "System.Data.OleDb");
            providerInvariantNames.Add(LDbType.ODBC, "System.Data.ODBC");
            providerInvariantNames.Add(LDbType.Oracle, "System.Data.OracleClient");
            providerInvariantNames.Add(LDbType.MySQL, "MySql.Data.MySqlClient");
            providerInvariantNames.Add(LDbType.SQLite, "System.Data.SQLite");
            providerInvariantNames.Add(LDbType.SQLServerCe, "System.Data.SqlServerCe");
        }
        /// <summary>
        /// 获取指定数据库类型对应的程序集名称
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        public static string GetProviderInvariantName(LDbType providerType)
        {
            return providerInvariantNames[providerType];
        }
        /// <summary>
        /// 获取指定类型的数据库对应的DbProviderFactory
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        public static DbProviderFactory GetDbProviderFactory(LDbType providerType, string conStr)
        {
            //如果还没有加载，则加载该DbProviderFactory
            if (!providerFactoies.ContainsKey(providerType))
            {
                providerFactoies.Add(providerType, ImportDbProviderFactory(providerType, conStr));
            }
            return providerFactoies[providerType];
        }
        /// <summary>
        /// 加载指定数据库类型的DbProviderFactory
        /// </summary>
        /// <param name="providerType">数据库类型枚举</param>
        /// <returns></returns>
        private static DbProviderFactory ImportDbProviderFactory(LDbType providerType, string conStr)
        {
            string providerName = providerInvariantNames[providerType];
            DbProviderFactory factory = null;
            try
            {
                //从全局程序集中查找
                switch (providerType)
                {
                    case LDbType.MySQL:
                        
                        //factory = new MySql.Data.MySqlClient.MySqlClientFactory();
                        #region DbProviderFactories.GetFactory()不能创建MySqlClientFactory解决方案
                        /*https://www.cnblogs.com/Araneid/archive/2011/01/07/1929531.html
                          <system.data>
    <DbProviderFactories>
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=5.1.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
    ...
    <pages>
      <namespaces>
        <add namespace="MySql.Data"/>
        <add namespace="MySql.Data.MySqlClient"/>
      </namespaces>
    </pages>
    <compilation debug="true">
            <assemblies>
        <add assembly="MySql.Data, Version=5.1.7.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"/>
      </assemblies>
    </compilation>
...
    </system.web>
                         */
                        #endregion
                        factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient"); 

                        break;
                    case LDbType.SQLite:
                        factory = (DbProviderFactory)Activator.CreateInstance(Type.GetType("System.Data.SQLite.SQLiteFactory.Instance"), false, true);
                        break;
                    default:
                        factory = DbProviderFactories.GetFactory(providerName);
                        break;
                }
            }
            catch (Exception ex)
            {
                factory = null;
                throw new Exception(ex.Message);
            }
            return factory;
        }
    }
}